package models

import play.api.db._
import play.api.Play.current

import anorm._
import anorm.SqlParser._

case class Deck(id: Pk[Long], name: String)

object Deck {
  // -- Parsers
  
  /**
   * Parse a Deck from a ResultSet
   */
  val simple = {
    get[Pk[Long]]("deck.id") ~/
    get[String]("deck.name") ^^ {
      case id~name => Deck(id, name)
    }
  }
  
  // -- Queries
    
  /**
   * Retrieve a Deck from id.
   */
  def findById(id: Long): Option[Deck] = {
    DB.withConnection { implicit connection =>
      SQL("select * from deck where id = {id}").on(
        'id -> id
      ).as(Deck.simple ?)
    }
  }
  
  /**
   * Retrieve deck for user
   */
  def findInvolving(user: String): Seq[Deck] = {
    DB.withConnection { implicit connection =>
      SQL(
        """
          select * from deck 
          join deck_owner on deck.id = deck_owner.deck_id 
          where deck_owner.user_email = {email}
        """
      ).on(
        'email -> user
      ).as(Deck.simple *)
    }
  }
  
  /**
   * Update a deck.
   */
  def rename(id: Long, newName: String) {
    DB.withConnection { implicit connection =>
      SQL("update deck set name = {name} where id = {id}").on(
        'id -> id, 'name -> newName
      ).executeUpdate()
    }
  }
  
  /**
   * Delete a deck.
   */
  def delete(id: Long) {
    DB.withConnection { implicit connection => 
      SQL("delete from deck where id = {id}").on(
        'id -> id
      ).executeUpdate()
    }
  }
  
  /**
   * Delete all deck in a folder
   */
  def deleteInFolder(folder: String) {
    // TODO: Remove
  }
  
  /**
   * Rename a folder
   */
  def renameFolder(folder: String, newName: String) {
    // TODO: Remove
  }
  
  /**
   * Retrieve deck member
   */
  def membersOf(deck: Long): Seq[User] = {
    DB.withConnection { implicit connection =>
      SQL(
        """
          select user.* from user 
          join deck_owner on deck_owner.user_email = user.email 
          where deck_owner.deck_id = {deck}
        """
      ).on(
        'deck -> deck
      ).as(User.simple *)
    }
  }
  
  /**
   * Add a member to the deck team.
   */
  def addMember(deck: Long, user: String) {
    DB.withConnection { implicit connection =>
      SQL("insert into deck_owner values({deck}, {user})").on(
        'deck -> deck,
        'user -> user
      ).executeUpdate()
    }
  }
  
  /**
   * Remove a member from the deck team.
   */
  def removeMember(deck: Long, user: String) {
    DB.withConnection { implicit connection =>
      SQL("delete from deck_owner where deck_id = {deck} and user_email = {user}").on(
        'deck -> deck,
        'user -> user
      ).executeUpdate()
    }
  }
  
  /**
   * Check if a user is a member of this deck
   */
  def isMember(deck: Long, user: String): Boolean = {
    DB.withConnection { implicit connection =>
      SQL(
        """
          select count(user.email) = 1 from user 
          join deck_owner on deck_owner.user_email = user.email 
          where deck_owner.deck_id = {deck} and user.email = {email}
        """
      ).on(
        'deck -> deck,
        'email -> user
      ).as(scalar[Boolean])
    }
  }
   
  /**
   * Create a Project.
   */
  def create(deck: Deck, members: Seq[String]): Deck = {
     DB.withTransaction { implicit connection =>
       // Get the deck id
       val id: Long = deck.id.getOrElse {
         SQL("select next value for deck_seq").as(scalar[Long])
       }
       
       // Insert the deck
       SQL(
         """
           insert into deck values (
             {id}, {name}
           )
         """
       ).on(
         'id -> id,
         'name -> deck.name
       ).executeUpdate()
       
       // Add members
       members.foreach { email =>
         SQL("insert into deck_owner values ({id}, {email})").on('id -> id, 'email -> email).executeUpdate()
       }
       
       deck.copy(id = Id(id))
     }
  }
}
